<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Model untuk melakukan koneksi dengan tabel surat & kategori
 *
 * @author brlnt
 */
class Surat_model extends CI_Model {
    //put your code here
    function Surat_model() {
        parent::__construct();
        $this->CI = get_instance();
    }
    
    function hasilQuery($hasil) {
        $result['hasil'] = $hasil;
        $result['row'] = $hasil->num_rows();
        return $result;
    }

    function insert_surat($data) {
        $this->db->insert('surat', $data);
		return $this->db->insert_id();
    }
	
	function edit_surat($id, $data){
		$this->db->where('idsurat', $id);
		$this->db->update('surat', $data);		
	}

	/**
	 * Mendapatkan surat berdasarkan id Kategori
	 */
    function getSuratByCategory($cat, $start=0, $limit=0) {		
        if($this->isParent($cat)){
            $children = array();
            $this->getChildren($cat, $children);

            $child = count($children);

            $query = "SELECT idsurat,kode_surat,nama_instansi,tanggal_kerjasama,masa_berlaku,kerjasama_mengenai,nomersurat,unit_terkait,scan_file ";
            $query .= "FROM surat ";
            $query .= "WHERE ";
            for($i=0;$i<$child;$i++){
                    $query .= "kategori_idkategori=".$children[$i];
                    if($i != $child-1){$query .= " OR ";}
            }
            $query .= " AND status=1 ";
			if($start+$limit != 0){
				$query .= " LIMIT ".$start.",".$limit;
			}
			
            $hasil = $this->db->query($query);
            return $hasil;
        } else{
            $this->db->select('idsurat,kode_surat,nama_instansi,tanggal_kerjasama,masa_berlaku,kerjasama_mengenai,nomersurat,unit_terkait,scan_file');
            $this->db->from('surat');
            $this->db->where('kategori_idkategori', $cat);
            $this->db->where('status', 1);
			if($start+$limit != 0){
				$this->db->limit($limit, $start);
			}
            $hasil = $this->db->get();
            return $hasil;
        }
    }
    
    /**
     * Mendapatkan semua id parent dari id child
     * param $idcat merupakan idchild
	 * return $datakategori {{$id, {nama_kategori}, {id_parent}}
     */
    function getCategories($idcat){
		$cat = $idcat;
		$datakategori = array();
		while($cat != 0){
			$this->db->select('*');
			$this->db->from('kategori');
			$this->db->where('idkategori', $cat);
			$hasil = $this->db->get();
			foreach($hasil->result() as $row){
				$detail['id'] = $row->idkategori;
				$detail['nama_kategori'] = $row->nama_kategori;
				$cat= $row->id_parent;
			}
			array_push($datakategori, $detail);
		}
		return $datakategori;
	}
	
	/**
	 * Mendapatkan id kategori berdasarkan id kategori
	 */
	function getCategoriesByID($id){
		$this->db->select('kategori_idkategori');
		$this->db->from('surat');
		$this->db->where('idsurat', $id);
		$query = $this->db->get();
		$hsl = $query->row();
		$idcat = $hsl->kategori_idkategori;
		return $this->getCategories($idcat);	
	}
	
	/**
	 * Mendapatkan semua child kategori berdasarkan parent
	 */
	function getChildren($cat, &$arr){
		$this->db->select('idkategori');
		$this->db->from('kategori');
		$this->db->where('id_parent', $cat);
		$hasil = $this->db->get();
		foreach($hasil->result() as $row){
			if($this->isParent($row->idkategori)){
				$this->getChildren($row->idkategori, $arr);
			} else{
				array_push($arr, $row->idkategori);
			}
		}
	}

	/**
	 * Memastikan bahwa id kategori merupakan id parent
	 */
	function isParent($cat){
		$this->db->select('id_parent');
		$this->db->from('kategori');
		$this->db->where('id_parent', $cat);
		$hasil = $this->db->get();
		if ($hasil->num_rows() > 0) {
			return true;
		} else {
			return false;
		}
	}
	
	/**
	 * Mendapatkan id kategori berdasarkan id parent
	 */
	function getCategoriesByParent($idparent){
		$this->db->select('*');
		$this->db->from('kategori');
		$this->db->where('id_parent', $idparent);
		return $this->db->get();
	}
	
	/**
	 * Mendapatkan surat berdasarkan idsurat
	 */
    function getSuratByID($id) {
        $this->db->select('*');
        $this->db->from('surat');
        $this->db->where('idsurat', $id);
        // set the result
        return $this->hasilQuery($this->db->get());
    }

    /**
     * Mendapatkan notifikasi untuk surat yang hampir/sudah kadaluwarsa
     * @param int $start, int $limit
     * @return query
     */
	function getNotifSurat($start=0, $limit=0) {
		// mengubah status surat kadaluwarsa = 0 (inactive)
		$query = "UPDATE surat SET status=0 WHERE now()>=masa_berlaku";
		$this->db->query($query);
		// mendapatkan data surat hampir kadaluwarsa
		$query = "SELECT idsurat,unit_terkait,kode_surat,nomersurat, masa_berlaku,nama_instansi, kerjasama_mengenai FROM surat WHERE date_add(now(), INTERVAL 3 MONTH) >= masa_berlaku AND status=1";
		if ($start+$limit != 0){
			$query .= " LIMIT ".$start.",".$limit;
		}
		$hasil = $this->db->query($query);
		return $hasil;
    }
}

?>
